package se581.banker.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.log4j.Logger;

import se581.banker.TrackerAccount;
import se581.banker.event.TrackerEvent;

public class UserAccountDao implements TrackerDao {
	static Logger logger = Logger.getLogger(UserAccountDao.class.getName());
	

	static final String FIND_BY_ID_SQL = "SELECT * FROM USR_ACCT WHERE ID=?";
    static final String NEW_ACCOUNT_SQL = "INSERT into USR_ACCT values(?, ?)";
    static final String UPDATE_ACCOUNT_SQL = "UPDATE USR_ACCT set BAL=BAL+? WHERE ID=?";
    static final String DELETE_ACCOUNT_SQL = "DELETE from USR_ACCT where ID=?";
    
    
    
   Connection conn = null;
   PreparedStatement pstmt = null;
   ResultSet rs = null;
   
   
   private Connection getConnection() throws SQLException {
	    Connection conn;
	    conn=ConnectionFactory.getInstance().getConnection();
	    return conn;
   }
   
   public void add(TrackerEvent ev) {
	   try
	   {
		   	conn=getConnection();
			PreparedStatement preparedStatement = conn
	        		.prepareStatement(UserAccountDao.NEW_ACCOUNT_SQL);
		    preparedStatement.setInt(1, Integer.parseInt(ev.getId()) );
		    preparedStatement.setInt(2, ev.getTransactionAmt());
		    int numRowsAffected = preparedStatement.executeUpdate();
		    
		    // New Id, insert
		    if (numRowsAffected < 1) {
		    	logger.error("Unable to add account:  " + ev.toEventLine() );
		    }
	   }
	   catch(SQLException e) {
		   e.printStackTrace();
	   }
	   finally {
		    try {
			    if(rs!=null)
			        rs.close();
			    if(pstmt!=null)
			       pstmt.close();
			    if(conn!=null)
			       conn.close();
		    }
		    catch(SQLException e)
		    {
		     e.printStackTrace();
		    }
		    catch(Exception e)
		    {
		     e.printStackTrace();
		    }		   
	   }
   }
   
   
   public void update(TrackerEvent ev) {
	   try
	   {
		   	conn=getConnection();
			PreparedStatement preparedStatement = conn
	        		.prepareStatement(UserAccountDao.UPDATE_ACCOUNT_SQL);
		    preparedStatement.setInt(1, ev.getTransactionAmt());
		    preparedStatement.setInt(2, Integer.parseInt(ev.getId()) ); 
		    int numRowsAffected = preparedStatement.executeUpdate();
		    
		    // New Id, insert
		    if (numRowsAffected < 1) {
		    	add(ev);
		    }
	   }
	   catch(SQLException e) {
		   e.printStackTrace();
	   }
	   finally {
		    try {
			    if(rs!=null)
			        rs.close();
			    if(pstmt!=null)
			       pstmt.close();
			    if(conn!=null)
			       conn.close();
		    }
		    catch(SQLException e)
		    {
		     e.printStackTrace();
		    }
		    catch(Exception e)
		    {
		     e.printStackTrace();
		    }		   
	   }	   
   }
   
   /**
    * Delete just for testing
    * 
    * @param ev
    */
   public void delete(String accountId) {
	   try
	   {
		   	conn=getConnection();
			PreparedStatement preparedStatement = conn
	        		.prepareStatement(UserAccountDao.DELETE_ACCOUNT_SQL);
		    preparedStatement.setInt(1, Integer.parseInt(accountId) );
		    int numRowsAffected = preparedStatement.executeUpdate();
		    
		    // New Id, insert
		    if (numRowsAffected < 1) {
		    	logger.error("Unable to delete account:  " + accountId );
		    }
	   }
	   catch(SQLException e) {
		   e.printStackTrace();
	   }
	   finally {
		    try {
			    if(rs!=null)
			        rs.close();
			    if(pstmt!=null)
			       pstmt.close();
			    if(conn!=null)
			       conn.close();
		    }
		    catch(SQLException e)
		    {
		     e.printStackTrace();
		    }
		    catch(Exception e)
		    {
		     e.printStackTrace();
		    }		   
	   }
   }
   
   public TrackerAccount findByPrimaryKey(String accountId) {
	   
	   TrackerAccount acct = null;
	   try
	   {
		   	conn=getConnection();
		    pstmt=conn.prepareStatement(UserAccountDao.FIND_BY_ID_SQL);
		    pstmt.setString(1, accountId);
		    rs=pstmt.executeQuery();
		    while (rs.next()) {
			     acct =  new TrackerAccount();
			     acct.setId( Integer.toString(rs.getInt(1)) );
			     acct.setBalance( rs.getInt(2) );
		    }
	   }
	   catch(SQLException e) {
		   e.printStackTrace();
	   }
	   finally {
		    try {
			    if(rs!=null)
			        rs.close();
			    if(pstmt!=null)
			       pstmt.close();
			    if(conn!=null)
			       conn.close();
		    }
		    catch(SQLException e)
		    {
		     e.printStackTrace();
		    }
		    catch(Exception e)
		    {
		     e.printStackTrace();
		    }		   
	   }
	   
	   return acct;
   }
}
